package com.woniuxy.mapper;

import com.woniuxy.pojo.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.woniuxy.pojo.dto.AgeDistributionDTO;
import com.woniuxy.pojo.dto.GenderStatisticsDTO;
import com.woniuxy.pojo.dto.RegionUserCountDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author Administrator
* @description 针对表【t_user】的数据库操作Mapper
* @createDate 2025-06-05 10:15:14
* @Entity com.woniuxy.pojo.User
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {

    @Select("SELECT region, COUNT(*) AS userCount FROM t_user GROUP BY region ORDER BY userCount DESC")
    List<RegionUserCountDTO> countUsersByRegion();

    @Select("SELECT " +
            "SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) AS male, " +
            "SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END) AS female " +
            "FROM t_user " +
            "WHERE gender IN ('男', '女')")
    GenderStatisticsDTO getGenderStatistics();

    @Select("SELECT '0-18岁' AS ageRange, COUNT(*) AS count FROM t_user WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 0 AND 18 " +
            "UNION ALL " +
            "SELECT '19-30岁', COUNT(*) FROM t_user WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 19 AND 30 " +
            "UNION ALL " +
            "SELECT '31-45岁', COUNT(*) FROM t_user WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 31 AND 45 " +
            "UNION ALL " +
            "SELECT '46-60岁', COUNT(*) FROM t_user WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) BETWEEN 46 AND 60 " +
            "UNION ALL " +
            "SELECT '60岁以上', COUNT(*) FROM t_user WHERE TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) > 60")
    List<AgeDistributionDTO> getAgeDistribution();
}




